﻿## The constants that we will use

$xlCenter = -4108
$xlCellValue = 1
$xlEqual = 3
$xl3TrafficLights2 = 5
$xl3Symbols = 7
$xlConditionValueNumber = 0
$xlIconSet = 6
$xlAutomatic = -4105
$xlBottom = -4107
$xlContinuous = 1
$xlThin = 2
$xlLocalSessionChanges = 2
$xlNoChange = 1
$xlWorkbookNormal = -4143

 

# Create an instance of Excel
$Excel = New-Object -comobject Excel.Application
$Excel.visible = $True 
$Workbook = $Excel.Workbooks.Add()
$Info = $Workbook.Worksheets.Item(1)
  
# Create our column headers
$Info.Cells.Item(1,1) = "Displayname"
$Info.Cells.Item(1,2) = "Disconnected Mailbox Exists"
$Info.Cells.Item(1,3) = "SID"
$Info.Cells.Item(1,4) = "Total Items"
$Info.Cells.Item(1,5) = "Total Size (MB)"
$Info.Cells.Item(1,6) = "Archive Description"

# Make Columns wide enough for content
$info.Range("A:A").entireColumn.ColumnWidth = 28
$info.Range("B:B").entireColumn.ColumnWidth = 25
$info.Range("C:C").entireColumn.ColumnWidth = 61
$info.Range("D:D").entireColumn.ColumnWidth = 10
$info.Range("E:E").entireColumn.ColumnWidth = 13
$info.Range("F:F").entireColumn.ColumnWidth = 39
  
# Add a little formatting to the column header
$Style = $Info.UsedRange  #Range object
$Style.Interior.ColorIndex = 19
$Style.Font.ColorIndex = 11
$Style.Font.Bold = $True
$Style.Borders.LineStyle = $xlContinuous
$Style.Borders.ColorIndex = 0
$Style.Borders.TintAndShade = 0
$Style.Borders.Weight = $xlThin

$Info.Range("B:B").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
$Info.Range("B:B").FormatConditions.Item(1).Interior.Color = 13551615
$Info.Range("B:B").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("B:B").FormatConditions.Item(1).Interior.TintAndShade = 0
$Info.Range("B:B").FormatConditions.Item(1).Font.Color = -16383844
$Info.Range("B:B").FormatConditions.Item(1).Font.TintAndShade = 0


$Info.Range("B:B").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
$Info.Range("B:B").FormatConditions.Item(2).Interior.Color = 13561798
$Info.Range("B:B").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("B:B").FormatConditions.Item(2).Interior.TintAndShade = 0
$Info.Range("B:B").FormatConditions.Item(2).Font.Color = -16752384
$Info.Range("B:B").FormatConditions.Item(2).Font.TintAndShade = 0

# Data starts at row 2
$intRow = 2

# iterate over each Archive entry
#foreach ($Archive in $f)
for ($i = 0;$i -le 10; $i++) 
{
 $Archive = $f[$i]
 $Style.Cells.Item($intRow, 1).Select()
 $Style.Cells.Item($intRow, 1) = $Archive.DisplayName
 $Style.Cells.Item($intRow, 2) = $Archive."Disconnected Mailbox Exists" 
 $Style.Cells.Item($intRow, 3) = $Archive."SID" 
 $Style.Cells.Item($intRow, 4) = $Archive."Total Items" 
 $Style.Cells.Item($intRow, 5) = $Archive."Total Size (MB)"
 $Style.Cells.Item($intRow, 6) = $Archive."Archive Description" 
 
 $intRow++
}

## Now for some formatting 
#  For more data on the enumeration values look at the following:
#  XlFormatConditionType Enumeration:       http://msdn.microsoft.com/en-us/library/bb241301.aspx 
#  XlFormatConditionOperator Enumeration: http://msdn.microsoft.com/en-us/library/bb241299.aspx
#  XlIconSet Enumeration                  http://msdn.microsoft.com/en-us/library/bb241324.aspx
#  XlConditionValueTypes Enumeration      http://msdn.microsoft.com/en-us/library/bb241028.aspx


# Make Column "A" wide enough for content
$info.Range("A:A").entireColumn.Autofit()
$info.Range("C:C").entireColumn.Autofit()
$info.Range("D:D").entireColumn.Autofit()
$info.Range("E:E").entireColumn.Autofit()
$info.Range("F:F").entireColumn.Autofit()

# Make Column "B" wide enough for content and Center the contents
$info.Range("B:B").entireColumn.Autofit()
$info.Range("B:B").HorizontalAlignment = $xlCenter 


$pathForReport = "U:\temp"
Remove-Item "$($pathForReport)\test.xlsx"
#$Workbook.SaveAs("$($pathForReport)\test.xlsx",$xlWorkbookNormal,"","",$true,$false,$xlNoChange,$xlLocalSessionChanges )
$Workbook.SaveAs("$($pathForReport)\test.xlsx")
$Excel.Quit()
